/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.NguoiDungPOJO;
import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author kimloan
 */
public class NguoiDungDAO {

    public boolean themNguoiDung(NguoiDungPOJO nd) {
        boolean kq = false;
        String sql = String.format("INSERT INTO NguoiDung(Username,Pass,Quyen)"
                + " VALUES('%s','%s',%d)", nd.getTenDangNhap(), nd.getMatKhau(), nd.getQuyen());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Them NguoiDung thanh cong
        }

        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xoa NguoiDung
     * @return
     */
    public boolean xoaNguoiDung(String maNguoiDung) {

        boolean kq = false;
        try {
            String sql = "delete from NguoiDung where idNguoiDung=" + maNguoiDung;
            // String sql = "DELETE FROM NguoiDung WHERE maNguoiDung ='"+  maNguoiDung+"'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            int n = helper.executeUpdate(sql);
            if (n >= 1) {
                kq = true;//xoa thanh cong
            }
            helper.close();
            return kq;
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return kq;
        }
    }

    /**
     * Phuong Thuc Cap Nhat NguoiDung
     */
    public boolean capNhatNguoiDung(NguoiDungPOJO nd) {
        boolean kq = false;

        String sql = String.format(
                "UPDATE nguoidung SET Username='%s',Pass='%s',Quyen=%d"
                + " WHERE idNguoiDung=%d ", nd.getTenDangNhap(), nd.getMatKhau(), nd.getQuyen(), nd.getMaNguoiDung());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Cap Nhat NguoiDung thanh cong
        }
        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xem Danh NguoiDung
     * @return
     */
    public ArrayList<NguoiDungPOJO> layDanhNguoiDungCacMaNguoiDung() {
        ArrayList<NguoiDungPOJO> ds = new ArrayList<NguoiDungPOJO>();
        try {
            String sql = "SELECT * FROM nguoidung";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                NguoiDungPOJO nd = new NguoiDungPOJO();
                nd.setMaNguoiDung(rs.getInt("idNguoiDung"));
                nd.setTenDangNhap(rs.getString("Username"));
                nd.setMatKhau(rs.getString("Pass"));
                nd.setQuyen(rs.getInt("Quyen"));
                ds.add(nd);
            }
            helper.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    /**
     * Phuong Thuc Lay Thong Tin NguoiDung
     * @param maNguoiDung
     * @return
     */
    public NguoiDungPOJO layThongTinNguoiDung(String maNguoiDung) {
        NguoiDungPOJO NguoiDung = null;
        String sql = String.format("SELECT * FROM NguoiDung WHERE idNguoiDung=%d", maNguoiDung);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                NguoiDung = new NguoiDungPOJO();
                NguoiDung.setMaNguoiDung(rs.getInt("idNguoiDung"));
                NguoiDung.setTenDangNhap(rs.getString("Username"));
                NguoiDung.setMatKhau(rs.getString("Pass"));
                NguoiDung.setQuyen(rs.getInt("Quyen"));

            }
            helper.close();
        } catch (SQLException ex) {

            System.out.println(ex.getMessage());
        }
        return NguoiDung;
    }
    //Ham Lay danh NguoiDung theo the loai

    public ArrayList<NguoiDungPOJO> layDSNguoiDung_TheoTheLoai(int maTheLoai) {

        ArrayList<NguoiDungPOJO> dsNguoiDung = new ArrayList<NguoiDungPOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LayDSNguoiDung_TheoTheLoai(?)}");
            statement.setInt(1, maTheLoai);

            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                NguoiDungPOJO NguoiDung = new NguoiDungPOJO();
                NguoiDung.setMaNguoiDung(rs.getInt("idNguoiDung"));
                NguoiDung.setTenDangNhap(rs.getString("Username"));
                NguoiDung.setMatKhau(rs.getString("Pass"));
                NguoiDung.setQuyen(rs.getInt("Quyen"));
                dsNguoiDung.add(NguoiDung);

            }
            helper.close();
            return dsNguoiDung;
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
            return dsNguoiDung;
        }
    }

    public ArrayList<NguoiDungPOJO> timKiemNguoiDung_TheoTenNguoiDung(String _tenNguoiDung) {

        ArrayList<NguoiDungPOJO> ds = new ArrayList<NguoiDungPOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LayNguoiDungTheoTenNguoiDung(?)}");
            statement.setString(1, _tenNguoiDung);
            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                NguoiDungPOJO NguoiDung = new NguoiDungPOJO();
                NguoiDung.setMaNguoiDung(rs.getInt("idNguoiDung"));
                NguoiDung.setTenDangNhap(rs.getString("Username"));
                NguoiDung.setMatKhau(rs.getString("Pass"));
                NguoiDung.setQuyen(rs.getInt("Quyen"));
                ds.add(NguoiDung);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return ds;
    }
}
